from os.path import dirname,abspath
import openpyxl
from openpyxl.styles import Alignment#加
from string import ascii_uppercase
from warnings import warn

def get_RowLetter(n=None):
    i=26
    letters=[char for char in ascii_uppercase]
    for char1 in ascii_uppercase:
        for char2 in ascii_uppercase:
            letters.append(char1+char2)
            if n!=None:
                i+=1
                if i==n:
                    break
    return letters

def cell_center(sheet,place,content):
    # 写入文本形式
    sheet[place] = content

    align_ = Alignment(
        horizontal='center',  # 水平对齐方式:center, left, right
        vertical='center'  # 垂直对齐方式: center, top, bottom
    )

    sheet[place].alignment = align_


def create_excel(contents,excel_name='/test.xlsx',sheet_name=''):# 加
    def create_sheet(sheet_name):
        if len(sheet_name)>31:
            warn('sheet名超过31个字符，需要截取')
            sheet_name=sheet_name.replace(' - ','-')
            if len(sheet_name) > 31:
                sheet_name = sheet_name[:31]
        sheet = workbook.create_sheet(sheet_name)



        i = 0
        for content in contents:
            cell_center(sheet, letters[i] + '1', content)

            i += 1
        # 读取
        #print(sheet['A1'].value)
        return sheet_name

    num=len(contents)
    if num==0:
        print('输入表格内容为零，请重新检查')
        exit()
    elif num>26*26:
        print('超过表格列表最大范围，请重新检查')
        exit()
    else:
        letters=get_RowLetter(num)

    path = dirname(abspath(__file__)) + excel_name
    print('init_excel的path:',path)
    # 创建一个工作本
    workbook = openpyxl.Workbook()
    # 获得sheet
    sheet = workbook['Sheet']
    # 移除sheet
    workbook.remove(sheet)
    # 创建一个 sheet
    if sheet_name=='':
        sheet_name=excel_name.split('/')[-1].split('.')[0]
    if type(sheet_name)==str:
        new_sheet_name=create_sheet(sheet_name)
    elif type(sheet_name)==list:
        new_sheet_name=[]
        for name in sheet_name:
            new_name=create_sheet(name)
            #print('new_name:',new_name)
            new_sheet_name.append(new_name)

    # 保存
    workbook.save(path)
    print('评论保存在', path)
    # workbook.close()
    return path,new_sheet_name

def get_sheet(path,sheet_name=''):
    if sheet_name == '':
        sheet_name = path.split('/')[-1].split('.')[0]
    wb = openpyxl.load_workbook(path)

    sheet = wb[sheet_name]

    return wb,sheet#原只有sheet

def write_excel(wb,path,sheet,contents_list,start=1):
    #contents_list数量不要超过26,都是空
    contents_num=len(contents_list)
    if contents_num == 0:
        print('输入表格内容为零，请重新检查')
        exit()
    elif contents_num > 26 * 26:
        print('超过表格列表最大范围，请重新检查')
        exit()
    else:
        letters=get_RowLetter(contents_num)

    print('开始写入表格')

    i = 0
    for contents in contents_list:
        #print('contents:', contents)
        id=start
        for content in contents:
            # print('content:',content)
            # cell_place=f'{ascii_uppercase[i]}{id+1}'
            # print('cell_place:',cell_place)
            cell_center(sheet,f'{letters[i]}{id+1}', content)#id+1不覆盖列名
            id+=1

        i+=1

    wb.save(path)
    wb.close()


